package com.sl.au.service.impl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.joda.time.DateTime;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.data.jpa.repository.Query;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import com.sl.au.entity.EventReport;
import com.sl.au.entity.GridUserDetails;
import com.sl.au.entity.EventToEvaluateUser;
import com.sl.au.entity.EventTask;
import com.sl.au.entity.EventType;
import com.sl.au.entity.Grading;
import com.sl.au.entity.GridAreaInfo;
import com.sl.au.entity.User;
import com.sl.au.entity.UserSingle;
import com.sl.au.entity.ViewForEventReportTask;
import com.sl.au.entity.Option;
import com.sl.au.entity.EventRegister;
import com.sl.au.repository.EventReportRepository;
import com.sl.au.repository.GridUserDetailsRepository;
import com.sl.au.repository.EventTaskRepository;
import com.sl.au.repository.EventTypeRepository;
import com.sl.au.repository.UserRepository;
import com.sl.au.repository.GradingRepository;
import com.sl.au.repository.GridAreaInfoRepository;
import com.sl.au.repository.EventRegisterRepository;
import com.sl.au.repository.ViewForEventReportTaskRepository;
import com.sl.au.repository.EventToEvaluateUserRepository;
import com.sl.au.repository.OptionRepository;
import com.sl.au.service.EventReportService;

@Service("EventReportService")
@Transactional
public class EventReportServiceImpl extends BaseServiceImpl implements EventReportService {
	@Autowired
	EventReportRepository eventReportRepository;
	@Autowired
	EventRegisterRepository eventRegisterRepository;
	@Autowired
	OptionRepository optionRepository;
	@Autowired
	EventTaskRepository eventTaskRepository;
	@Autowired
	ViewForEventReportTaskRepository viewForEventReportTaskRepository;
	@Resource
	private JdbcTemplate jdbcTemplate;
	@Autowired
	private GradingRepository gradingRepository;
	@Autowired
	private UserRepository userRepository;
	@Autowired
	private EventTypeRepository eventTypeRepository;
	@Autowired
	private EventToEvaluateUserRepository eventToEvaluateUserRepository;
	@Resource
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

	@Autowired
	private GridUserDetailsRepository gridUserDetailsRepository;

	public EventReportRepository getRepository() {
		return this.eventReportRepository;
	}

	@Override
	public List<EventReport> getPage(int start, int length, String key, String orderName, Direction dir) {
		Page<EventReport> pages = null;
		Pageable pageable = new PageRequest(start / length, length, dir, orderName);
		if (StringUtils.hasText(key)) {
			pages = this.eventReportRepository.getByKey(key, pageable);
		} else {
			pages = this.eventReportRepository.findAll(pageable);
		}

		return pages.getContent();
	}

	@Override
	public void finishEventReport(String id, String finishMemo, String reportEventAttrVariable, User user) {
		EventReport report = this.eventReportRepository.findOne(id);
		if (report != null && !report.getReportEventVariable().getTitle().equals("已完结")) {
			List<Option> list = this.optionRepository.findByGroupAndTitle("事件上报状态", "已完结");
			if (list.size() > 0) {
				report.setReportEventVariable(list.get(0));
			}
			report.setReportEventAttrVariable(this.optionRepository.findOne(reportEventAttrVariable));
			List<EventTask> taskList = this.eventTaskRepository.getNewestTaskByEventId(report.getId());

			EventTask task = null;
			if (taskList == null || taskList.size() <= 0
					|| taskList.get(0).getEventTaskState().getTitle().equals("已完结")) {
				return;
			}

			task = new EventTask();
			task.setEventId(report.getId());
			task.setEventTaskMemo(finishMemo);
			task.setEventTaskState(report.getReportEventVariable());
			task.setEventTaskTime(DateTime.now().toDate());
			task.setEventTaskUser(user);
			this.eventReportRepository.save(report);
			this.eventTaskRepository.save(task);
		}
	}

	@Override
	public List<EventReport> getPageByEventVariable(int start, int length, String key, String eventVariable,
			String orderName, Direction dir) {
		Page<EventReport> pages = null;
		Pageable pageable = new PageRequest(start / length, length, dir, orderName);
		if (StringUtils.hasText(key)) {
			pages = this.eventReportRepository.getByEventVariableAndKey(eventVariable, key, pageable);
		} else {
			pages = this.eventReportRepository.getByEventVariable(eventVariable, pageable);
		}

		return pages.getContent();
	}

	@Override
	public List<ViewForEventReportTask> getViewForEventReportTaskPageByEventVariable(int start, int length, String key,
			String eventVariable, String orderName, Direction dir) {
		Page<ViewForEventReportTask> pages = null;
		Pageable pageable = new PageRequest(start / length, length, dir, orderName);
		if (StringUtils.hasText(key)) {
			pages = this.viewForEventReportTaskRepository.getByEventVariableAndKey(eventVariable, key, pageable);
		} else {
			pages = this.viewForEventReportTaskRepository.getByEventVariable(eventVariable, pageable);
		}

		return pages.getContent();
	}

	@Override
	public int getCountByEventVariable(String key, String eventVariable) {
		Pageable pageable = new PageRequest(0, Integer.MAX_VALUE);
		if (!StringUtils.hasText(key))
			return this.eventReportRepository.getByEventVariable(eventVariable, pageable).getContent().size();
		return this.eventReportRepository.getByEventVariableAndKey(eventVariable, key, pageable).getContent().size();
	}

	@Override
	public int getViewForEventReportTaskCountByEventVariable(String key, String eventVariable) {
		Pageable pageable = new PageRequest(0, Integer.MAX_VALUE);
		if (!StringUtils.hasText(key))
			return this.viewForEventReportTaskRepository.getByEventVariable(eventVariable, pageable).getContent()
					.size();
		return this.viewForEventReportTaskRepository.getByEventVariableAndKey(eventVariable, key, pageable).getContent()
				.size();
	}

	@Override
	public int getCount(String key) {
		if (!StringUtils.hasText(key))
			return this.eventReportRepository.findAll().size();
		Pageable pageable = new PageRequest(0, Integer.MAX_VALUE);
		return this.eventReportRepository.getByKey(key, pageable).getContent().size();
	}

	@Override
	public void saveAll(EventReport eventReport, List<EventRegister> list, EventTask eventReportTask,
			List<EventTask> eventRegisterTask) {

		List<EventToEvaluateUser> eventToEvaluateUsers = new LinkedList<>();

		for (int i = 0; i < list.size(); i++) {
			double typeWeigth = 1;
			double areaWeight = 1;
			if (list.get(i).getRegisterEventType() != null) {
				EventType a = this.eventTypeRepository.getOne(list.get(i).getRegisterEventType().getId());
				typeWeigth = (double) a.getEventWeight();
			}
			if (list.get(i).getRegisterEventToNetGrid() != null) {
				areaWeight = (double) list.get(i).getRegisterEventToNetGrid().getGridScale();
			}

			if (list.get(i).getRegisterEventToNetGrid() != null) {
				List<GridUserDetails> gridUserDetails = this.gridUserDetailsRepository
						.getByGrid(list.get(i).getRegisterEventToNetGrid().getId());
				for (int j = 0; j < gridUserDetails.size(); j++) {
					EventToEvaluateUser user = new EventToEvaluateUser();
					user.setRegisterEvent(list.get(i));
					user.setRegisterEventEvaluateLevel(gridUserDetails.get(j).getGrading());
					BigDecimal b = new BigDecimal(
							gridUserDetails.get(j).getGrading().getGradingScale() * typeWeigth * areaWeight);
					double f = b.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
					user.setScale(f);
					user.setRegisterUser(gridUserDetails.get(j).getUser());
					eventToEvaluateUsers.add(user);
				}
			}
			/*
			 * List<Map<String, Object>> arrys = getDefaultEvaluateUser(
			 * list.get(i).getRegisterEventEvaluateLevel().getId(),
			 * list.get(i).getRegisterEventToNetGrid()); List<String> ids = new
			 * LinkedList<>();
			 * 
			 * 
			 * 
			 * for (Map<String, Object> arry : arrys) { ids.add((String)
			 * arry.get("id")); } List<User> userList =
			 * this.userRepository.findAll(ids); for (int j = 0; j <
			 * userList.size(); j++) { EventToEvaluateUser user = new
			 * EventToEvaluateUser(); user.setRegisterEvent(list.get(i));
			 * user.setRegisterEventEvaluateLevel(userList.get(j).getGrading());
			 * user.setScale(userList.get(j).getGrading().getGradingScale() *
			 * typeWeigth * areaWeight); user.setRegisterUser(userList.get(j));
			 * eventToEvaluateUsers.add(user); }
			 */
		}
		this.eventReportRepository.save(eventReport);
		this.eventRegisterRepository.save(list);
		this.eventTaskRepository.save(eventReportTask);
		this.eventTaskRepository.save(eventRegisterTask);
		this.eventToEvaluateUserRepository.save(eventToEvaluateUsers);

	}

	private List<Map<String, Object>> getDefaultEvaluateUser(String levelId, GridAreaInfo grid) {
		List<Map<String, Object>> arrys = new LinkedList<>();

		Grading grad = this.gradingRepository.findOne(levelId);
		/*
		 * String sql =
		 * "select sl_au_user.id,sl_au_user.`name` as username,sl_au_user.sContactType as userphone,sl_au_user.grading,sl_dcms_grading.gradingName,sl_dcms_grading.gradingScale as scale"
		 * +
		 * ",sl_dcms_option.sExtended1 from sl_au_user left JOIN sl_dcms_grading on sl_dcms_grading.id= sl_au_user.grading "
		 * +
		 * " left join sl_dcms_option on sl_dcms_option.id= sl_dcms_grading.gradingLevel_id where grading is not null and sl_dcms_grading.gradingParent is not null and cast(sl_dcms_option.sExtended1 as SIGNED INTEGER)>="
		 * + grad.getGradingLevel().getExtended1() + "" +
		 * " order by cast(sl_dcms_option.sExtended1 as SIGNED INTEGER) ASC";
		 */
		if (grad != null && grad.getGradingLevel() != null && grid != null) {
			String sql = "select sl_au_user.id,sl_au_user.`name` as username," + "sl_au_user.sContactType as userphone,"
					+ "sl_wcsi_grid_user_details_where.grading,sl_dcms_grading.gradingName,"
					+ "sl_dcms_grading.gradingScale as scale," + "sl_dcms_option.sExtended1 from sl_au_user "
					+ "left join (select * from sl_wcsi_grid_user_details where grid='" + grid.getId()
					+ "') as sl_wcsi_grid_user_details_where on sl_au_user.id=sl_wcsi_grid_user_details_where.`user` "
					+ "left JOIN sl_dcms_grading on sl_dcms_grading.id= sl_wcsi_grid_user_details_where.grading  "
					+ "left join sl_dcms_option on sl_dcms_option.id= sl_dcms_grading.gradingLevel_id "
					+ "where sl_wcsi_grid_user_details_where.grading is not null "
					+ "and cast(sl_dcms_option.sExtended1 as SIGNED INTEGER)>=" + grad.getGradingLevel().getExtended1()
					+ " "
					+ "order by cast(sl_dcms_option.sExtended1 as SIGNED INTEGER) ASC,sl_dcms_grading.gradingScale DESC";

			arrys = this.jdbcTemplate.queryForList(sql);
		}
		return arrys;
	}

	@Override
	public Map<String, Object> getStatistics() {
		Map<String, Object> map = new HashMap<String, Object>();
		String sql = "select count(*) as num,sTitle from (select sl_wcsi_eventreport.id, sl_dcms_option.sTitle from sl_wcsi_eventreport left join sl_dcms_option ON sl_wcsi_eventreport.reportEventVariable=sl_dcms_option.id where sl_dcms_option.id is not null) as a GROUP BY sTitle";
		List<Map<String, Object>> arrys = this.jdbcTemplate.queryForList(sql);
		int finishreportNum = 0;// 已处理
		int dealreportNum = 0;// 受理中
		int undealreportNum = 0;// 未处理
		int realreportNum = 0;// 实时告警总数

		for (Map<String, Object> arry : arrys) {
			Object obj = arry.get("num");
			realreportNum += Integer.parseInt(arry.get("num").toString());
			if (arry.get("sTitle") == null) {
				undealreportNum += Integer.parseInt(arry.get("num").toString());
			} else {
				String title = (String) arry.get("sTitle");
				if (title.equals("初始化")) {
					undealreportNum += Integer.parseInt(arry.get("num").toString());
				} else if (title.equals("已受理")) {
					dealreportNum += Integer.parseInt(arry.get("num").toString());
				} else {
					finishreportNum += Integer.parseInt(arry.get("num").toString());
				}
			}
		}
		map.put("finishreportNum", finishreportNum);// 已处理
		map.put("dealreportNum", dealreportNum);// 受理中
		map.put("undealreportNum", undealreportNum);// 未处理
		map.put("realreportNum", realreportNum);// 实时报警总数
		return map;
	}

	@Override
	public int getWaitingAcceptEventCountByAccount(String aid) {
		String sql = "SELECT count(*) as num from sl_wcsi_eventreport where sl_wcsi_eventreport.reportEventVariable in(select id from sl_dcms_option where sl_dcms_option.sTitle='初始化' and sl_dcms_option.sGroup='事件上报状态')";
		Integer num = jdbcTemplate.queryForObject(sql, Integer.class);
		return num;
	}

	@Override
	public List<EventReport> getFinishedReportPageByKey(int start, int length, String key, String optionId) {
		Pageable pageable = new PageRequest(start / length, length, Sort.Direction.DESC, "createTime");
		return eventReportRepository.getFinishedReportPageByKey(key, optionId, pageable).getContent();
	}

	@Override
	public List<EventReport> getFinishedReportPageNoKey(int start, int length, String optionId) {
		Pageable pageable = new PageRequest(start / length, length, Sort.Direction.DESC, "createTime");
		return eventReportRepository.getFinishedReportPageNoKey(optionId, pageable).getContent();
	}

	@Override
	public int getFinishedReportCount(String key, String optionId) {
		Pageable pageable = new PageRequest(0, Integer.MAX_VALUE, Sort.Direction.DESC, "createTime");
		if (!StringUtils.hasText(key)) {
			return eventReportRepository.getFinishedReportPageNoKey(optionId, pageable).getContent().size();
		}
		return eventReportRepository.getFinishedReportPageByKey(key, optionId, pageable).getContent().size();
	}

	@Override
	public List<EventReport> getEventReportByUser(int start, int length, User user, String orderName, Direction dir) {
		Pageable pageable = new PageRequest(start / length, length, dir, orderName);
		Page<EventReport> pages = null;
		pages = eventReportRepository.findByReportUser(user, pageable);
		return pages.getContent();
	}

	@Override
	public List<EventTask> findProcedures(String reportId) {

		return eventReportRepository.findProcedures(reportId);
	}

	@Override
	public List<EventReport> getEventReportByEventVariable(String eventVariable) {
		Page<EventReport> pages = null;
		Pageable pageable = new PageRequest(0, Integer.MAX_VALUE);
		pages = this.eventReportRepository.getByEventVariable(eventVariable, pageable);
		return pages.getContent();
	}

	@Override
	public List<EventReport> getReportPageByUserAndType(int start, int length, String user, String type,
			String orderName, Direction dir) {
		Page<EventReport> pages = null;
		Pageable pageable = new PageRequest(start / length, length, dir, orderName);
		pages = eventReportRepository.getReportPageByUserAndType(user, type, pageable);
		return pages.getContent();
	}

	@Override
	public List<EventReport> getReportPageByUserAndTypes(int start, int length, String user, String[] types,
			String orderName, Direction dir) {

		Page<EventReport> pages = null;
		Pageable pageable = new PageRequest(start / length, length, dir, orderName);
		pages = eventReportRepository.getReportPageByUserAndTypes(user, types, pageable);
		return pages.getContent();
	}

	@Override
	public List<ViewForEventReportTask> getReportByGrid(String gridId, String type, Date begin, Date end) {
		return viewForEventReportTaskRepository.getReportByGrid(gridId, type, begin, end);
	}

	@Override
	public Map<String, Object> getReportDataByGrid(String gridId, String type, Date begin, Date end) {
		Map<String, Object> resultMap = new HashMap<String, Object>();
		// 参数设置
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("gridId", gridId);
		params.put("type", type);
		params.put("begin", begin);
		params.put("end", end);

		List<Map<String, Object>> eventList = new ArrayList<Map<String, Object>>();
		String GridSQL = "SELECT e.eventName as `name`,COUNT(1) as `value` " + " FROM" + " viewForEventReportTask e "
				+ " WHERE e.reportAccTime >:begin and e.reportAccTime <:end and e.reportEventToNetGrid = :gridId AND e.eventTaskStateTitle = :type "
				+ " GROUP BY e.eventName ";

		eventList = namedParameterJdbcTemplate.queryForList(GridSQL, params);
		this.setAnalysisData(resultMap, eventList);

		return resultMap;
	}

	public void setAnalysisData(Map<String, Object> map, List<Map<String, Object>> list) {
		String[] legend = null;
		Object[] series = null;
		if (list != null && list.size() > 0) {
			legend = new String[list.size()];
			series = new Object[list.size()];
			int i = 0;
			for (Map<String, Object> mapTemp : list) {
				String name = (String) mapTemp.get("name");
				legend[i] = name;
				series[i++] = mapTemp;
			}
		} else {
			legend = new String[0];
			series = new Object[0];
		}
		map.put("legend", legend);
		map.put("series", series);
	}

	@Override
	public List<EventReport> findInitedInfo(String id, double latitude, double longitude, EventType eventType) {

		return eventReportRepository.findInitedInfo(id, latitude, longitude, eventType);
	}

	@Override
	public List<EventReport> findInitedInfo(int start, int length, String id, double latitude, double longitude,
			EventType eventType, String addr) {
		Pageable pageable = new PageRequest(start / length, length, Sort.Direction.DESC, "createTime");
		return eventReportRepository.findInitedInfo(pageable, id, latitude, longitude, eventType, addr).getContent();
	}

	@Override
	public int findInitedInfoCount(String id, double latitude, double longitude, EventType eventType, String addr) {
		
		
		return eventReportRepository.findInitedInfoCount(id, latitude, longitude, eventType, addr);
	}
	
	
	@Override
	public List<Map<String, Object>> getPageByEventVariableAndRole(int start, int length, String roleId, String key, String eventVariable, String orderName, String dir){
		String SQL1 = "select  a.*, "
				+ " et.eventName as 'reportEventTypeName', "
				+ " et.iconAddress as 'reportEventTypeIconAddress', "
				+ " u.name as 'reportUserName', "
				+ " o.sTitle as 'reportEventOrgTitle' "
				+ " from sl_wcsi_EventReport a "
				+ " left join sl_dcms_eventType et on et.id = a.reportEventType "
				+ " left join sl_au_user u on u.id = a.reportUser "
				+ " left join sl_dcms_option o on o.id = a.reportEventOrg "
				+ " left join sl_dcms_option v on v.id = a.reportEventVariable "
				+ " where 1 = 1 ";
		
		if(StringUtils.hasText(eventVariable)){
			SQL1+=" and v.sTitle='"+eventVariable+"'";
		}
		
		if(StringUtils.hasText(key)){
			SQL1+=" and (a.reportContent like '%"+key+"%' or et.eventName like '%"+key+"%') ";	
		}
		
		
		String SQL = " select sq.* from (select e2r.* from sl_wcsi_e2r e2r where e2r.roleId = '"+ roleId +"' ) er left join ( " + SQL1 + " ) sq on er.eventTypeId = sq.realReportEventType where sq.id is not null ";
		
		SQL += " order by sq." + orderName + " " + ( StringUtils.hasText(dir) ? dir : "" );
		SQL += " limit "+ (int)(Math.floor(start / length) * length) + " , " +  (int)((Math.floor(start / length) + 1) * length);
		
		List<Map<String, Object>> resultList = this.jdbcTemplate.queryForList(SQL);
		
		return resultList;
	}
	
	@Override
	public int getCountByEventVariableAndRole(String roleId, String key, String eventVariable) {
		String SQL1 = "select  a.*, "
				+ " et.eventName as 'reportEventTypeName', "
				+ " u.name as 'reportUserName', "
				+ " o.sTitle as 'reportEventOrgTitle' "
				
				+ " from sl_wcsi_EventReport a "
				+ " left join sl_dcms_eventType et on et.id = a.reportEventType "
				+ " left join sl_au_user u on u.id = a.reportUser "
				+ " left join sl_dcms_option o on o.id = a.reportEventOrg "
				+ " left join sl_dcms_option v on v.id = a.reportEventVariable "
				+ " where 1 = 1 ";
		
		if(StringUtils.hasText(eventVariable)){
			SQL1+=" and v.sTitle='"+eventVariable+"'";
		}
		
		if(StringUtils.hasText(key)){
			SQL1+=" and (a.reportContent like '%"+key+"%' or et.eventName like '%"+key+"%') ";	
		}
		String SQL = " select count(1) from (select e2r.* from sl_wcsi_e2r e2r where e2r.roleId = '"+ roleId +"' ) er left join ( " + SQL1 + " ) sq on er.eventTypeId = sq.realReportEventType where sq.id is not null ";
		
		int count = 0;
		count = this.jdbcTemplate.queryForObject(SQL, Integer.class);
		
		return count;
	}

	@Override
	public List<Map<String, Object>> getViewForEventReportTaskPageByEventVariableAndRole(int start, int length, String roleId, String key, String eventVariable, String orderName, String dir){
		
		//"select a from ViewForEventReportTask a where a.reportEventVariableTitle=?1 and a.eventTaskStateTitle=?1 and (a.reportContent like %?2% or a.eventName like %?2%)")
		
		String SQL1 = "select a.*, "
				+ " et.iconAddress as 'reportEventTypeIconAddress' "
				+ " from ViewForEventReportTask a "
				+ " left join sl_dcms_eventType et on et.id = a.reportEventType "
				+ " where 1 = 1 ";
		
		if(StringUtils.hasText(eventVariable)){
			SQL1+=" and a.reportEventVariableTitle='"+eventVariable+"' and a.eventTaskStateTitle ='"+eventVariable+"' ";
		}
		
		if(StringUtils.hasText(key)){
			SQL1+=" and (a.reportContent like '%"+key+"%' or a.eventName like '%"+key+"%') ";	
		}
		
		String SQL = " select sq.* from (select e2r.* from sl_wcsi_e2r e2r where e2r.roleId = '"+ roleId +"' ) er left join ( " + SQL1 + " ) sq on er.eventTypeId = sq.realReportEventType where sq.id is not null ";
		
		SQL += " order by sq." + orderName + " " + ( StringUtils.hasText(dir) ? dir : "" );
		SQL += " limit "+ (int)(Math.floor(start / length) * length) + " , " +  (int)((Math.floor(start / length) + 1) * length);
		
		List<Map<String, Object>> resultList = this.jdbcTemplate.queryForList(SQL);
		
		return resultList;
	}
	
	@Override
	public int getViewForEventReportTaskCountByEventVariableAndRole(String roleId, String key, String eventVariable) {
		String SQL1 = "select * "
				+ " from ViewForEventReportTask a "
				+ " where 1 = 1 ";
		
		if(StringUtils.hasText(eventVariable)){
			SQL1+=" and a.reportEventVariableTitle='"+eventVariable+"' and a.eventTaskStateTitle ='"+eventVariable+"' ";
		}
		
		if(StringUtils.hasText(key)){
			SQL1+=" and (a.reportContent like '%"+key+"%' or a.eventName like '%"+key+"%') ";	
		}
		
		String SQL = " select count(1) from (select e2r.* from sl_wcsi_e2r e2r where e2r.roleId = '"+ roleId +"' ) er left join ( " + SQL1 + " ) sq on er.eventTypeId = sq.realReportEventType where sq.id is not null ";
		
		int count = 0;
		count = this.jdbcTemplate.queryForObject(SQL, Integer.class);
		
		return count;
	}
	
	@Override
	public List<Map<String, Object>> getFinishedReportPageByKeyAndRole(int start, int length, String roleId, String key, String optionId, String orderName, String dir){
		
		//  @Query("select a from EventReport a where a.reportEventVariable.id=?2 and (a.reportContent like %?1% or a.reportUser.name like %?1%)")
		
		String SQL1 = "select  a.id as 'id',  a.createTime as 'createTime', "
				+ " a.reportAccTime as 'reportAccTime', "
				+ " a.reportAdress as 'reportAdress', "
				+ " a.reportContent as 'reportContent',"
				+ " a.reportEventVariable as 'reportEventVariable',"
				+ " a.reportEventType as 'reportEventType', "
				+ " a.realReportEventType as 'realReportEventType', "
				+ " et.eventName as 'reportEventTypeName', "
				+ " u.name as 'reportUserName', "
				+ " o.sTitle as reportEventOrgTitle, "
				+ " a.latitude as 'latitude', "
				+ " a.longitude as 'longitude', "
				+ " a.reportMemo as 'reportMemo' "
				+ " from sl_wcsi_EventReport a "
				+ " left join sl_dcms_eventType et on et.id = a.reportEventType "
				+ " left join sl_au_user u on u.id = a.reportUser "
				+ " left join sl_dcms_option o on o.id = a.reportEventOrg "
				+ " left join sl_dcms_option v on v.id = a.reportEventVariable "
				+ " where 1 = 1 ";
		
		if(StringUtils.hasText(optionId)){
			SQL1+=" and a.reportEventVariable='"+optionId+"'";
		}
		
		if(StringUtils.hasText(key)){
			SQL1+=" and (a.reportContent like '%"+key+"%' or u.name like '%"+key+"%') ";	
		}
		
		
		String SQL = " select sq.* from (select e2r.* from sl_wcsi_e2r e2r where e2r.roleId = '"+ roleId +"' ) er left join ( " + SQL1 + " ) sq on er.eventTypeId = sq.realReportEventType where sq.id is not null ";
		
		SQL += " order by sq." + orderName + " " + ( StringUtils.hasText(dir) ? dir : "" );
		SQL += " limit "+ (int)(Math.floor(start / length) * length) + " , " +  (int)((Math.floor(start / length) + 1) * length);
		
		List<Map<String, Object>> resultList = this.jdbcTemplate.queryForList(SQL);
		
		return resultList;
	}
	
	@Override
	public int getFinishedReportCountByRole(String roleId, String key, String optionId) {
		String SQL1 = "select  a.id as 'id',  a.createTime as 'createTime', "
				+ " a.reportAccTime as 'reportAccTime', "
				+ " a.reportAdress as 'reportAdress', "
				+ " a.reportContent as 'reportContent',"
				+ " a.reportEventVariable as 'reportEventVariable',"
				+ " a.reportEventType as 'reportEventType', "
				+ " a.realReportEventType as 'realReportEventType', "
				+ " et.eventName as 'reportEventTypeName', "
				+ " u.name as 'reportUserName', "
				+ " o.sTitle as reportEventOrgTitle, "
				+ " a.latitude as 'latitude', "
				+ " a.longitude as 'longitude' "
				+ " from sl_wcsi_EventReport a "
				+ " left join sl_dcms_eventType et on et.id = a.reportEventType "
				+ " left join sl_au_user u on u.id = a.reportUser "
				+ " left join sl_dcms_option o on o.id = a.reportEventOrg "
				+ " left join sl_dcms_option v on v.id = a.reportEventVariable "
				+ " where 1 = 1 ";
		
		if(StringUtils.hasText(optionId)){
			SQL1+=" and a.reportEventVariable='"+optionId+"'";
		}
		
		if(StringUtils.hasText(key)){
			SQL1+=" and (a.reportContent like '%"+key+"%' or u.name like '%"+key+"%') ";	
		}
		
		String SQL = " select count(1) from (select e2r.* from sl_wcsi_e2r e2r where e2r.roleId = '"+ roleId +"' ) er left join ( " + SQL1 + " ) sq on er.eventTypeId = sq.realReportEventType	 where sq.id is not null ";
		
		int count = 0;
		count = this.jdbcTemplate.queryForObject(SQL, Integer.class);
		
		return count;
	}

	@Override
	public int getWaitingAcceptEventCountByAccountByRole(String roleId, String aid) {
		String SQL1 = "select * from sl_wcsi_eventreport where sl_wcsi_eventreport.reportEventVariable in ( select id from sl_dcms_option where sl_dcms_option.sTitle='初始化' and sl_dcms_option.sGroup='事件上报状态')";
		
		String SQL = " select count(1) from (select e2r.* from sl_wcsi_e2r e2r where e2r.roleId = '"+ roleId +"' ) er left join ( " + SQL1 + " ) sq on er.eventTypeId = sq.reportEventType where sq.id is not null ";
		
		int count = 0;
		count = this.jdbcTemplate.queryForObject(SQL, Integer.class);
		
		return count;
	}

	@Override
	public List<EventReport> getPageByEventDate(int start, int length, Date begin, Date end, String orderName,
			Direction dir) {
		Page<EventReport> pages = null;
		Pageable pageable = new PageRequest(start / length, length, dir, orderName);
		
		pages = eventReportRepository.getPageByEventDate(begin, end, pageable);
		
		return pages.getContent();
	}

	@Override
	public int getCountByEventDate(Date begin, Date end) {
		int count = 0;
		count = eventReportRepository.getPageCountByEventDate(begin, end);
		return count;
	}
}